<?php 
/**
 *
 * @category    API
 * @package     Api_Exchange
 * @subpackage  Model
 * @author      trungpm
 */
class Api_Model_Exchange {
	const table_name = 'tblvgt_exchange';
	/* 
	* delete exchange
	* trungpm
	*/
    function DeleteExchange($ImportDate, $partnerId, $partnerPrice, $user, $password) {
        $Errors = array();
        $db = db($user, $password);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        }
		
        $sql = 'DELETE FROM tblvgt_exchange WHERE 
        	exchange_imported_date = N\''.$ImportDate.'\' AND partner_id = ' . $partnerId . ' AND exchange_price = ' . $partnerPrice;
        if (sizeof($Errors) == 0) {			
            return DB_Query($sql, $db);
        }
        return null;
    }

	/* Insert a new Exchange */
    function InsertExchange($ExchangeDetails, $user, $password) {
        $Errors = array();
        $db = db($user, $password);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        }
        foreach ($ExchangeDetails as $key=>$value) {
            $ExchangeDetails[$key] = DB_escape_string($value);
        }			
		$FieldNames = '';
        $FieldValues = '';
        foreach ($ExchangeDetails as $key=>$value) {
            $FieldNames .= $key.', ';
            $FieldValues .= 'N\''.str_replace("\'", "''", $value).'\', ';
        }
        $sql = 'INSERT INTO tblvgt_exchange ('.substr($FieldNames, 0, -2).') '.'VALUES ('.substr($FieldValues, 0, -2).') ';
        if (sizeof($Errors) == 0) {
            $result = DB_Query($sql, $db);
            if (!$result) {
                $Errors[0] = DatabaseInsertFailed;
            }
        }
        return $Errors;
    }
	
	/* Get Yield Revenue */
	function GetYieldRevenue($PartnerId = 2, $PartnerPrice = '', $ObjectId, $Type = 1, $FromDate, $ToDate, 
	$SingerAuthority = null, $ComposerAuthority = null, $ReleaseAuthority = null,  
	$limit = null, $start = null, &$total, $user, $password) {
		$Errors = array();
        $db = db($user, $password);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        }
		
		$sql = "SELECT row_number() OVER (ORDER BY exchange_imported_date, singer_name, 
					song_name, exchange_quantity, composer_name) AS rownum, 
					exchange_id, dbo.fnFormatDate (exchange_imported_date,'MM/YYYY') as exchange_imported_date, partner_id, partner_name, 
					song_number, song_name, singer_name, song_singer_sing, composer_name, exchange_price,
					exchange_quantity, 
					song_singer_authority, song_composer_authority, song_release_authority,
					(exchange_price*exchange_quantity*0.85) as money_total,
					(exchange_price*exchange_quantity*0.85)*0.4 as money_composer,
					(exchange_price*exchange_quantity*0.85)*0.3 as money_singer,
					(exchange_price*exchange_quantity*0.85)*0.3 as money_release, singer_id, composer_id
				FROM tblvgt_exchange
				WHERE locked = 0 AND exchange_imported_date >= '$FromDate'
					AND exchange_imported_date <= '$ToDate'";
		// neu tim ca sy
		if(isset($ObjectId) && ($ObjectId > 0)){
			if($Type == 1){
				$sql .= " AND singer_id = $ObjectId";
			}elseif($Type == 2){
				$sql .= " AND composer_id = $ObjectId";
			}
		}
		if(isset($PartnerId) && ($PartnerId > 0)){			
			$sql .= " AND partner_id = $PartnerId";			
		}
		if(isset($SingerAuthority)){			
			$sql .= " AND song_singer_authority = $SingerAuthority";			
		}
		if(isset($ComposerAuthority)){			
			$sql .= " AND song_composer_authority = $ComposerAuthority";			
		}
		if(isset($ReleaseAuthority)){			
			$sql .= " AND song_release_authority = $ReleaseAuthority";			
		}
		if(!empty($PartnerPrice)) {
			$sql .= " AND exchange_price = $PartnerPrice";
		}
		
		$sql1 .= "SELECT count(*) as c FROM(".$sql.") AS A";
		
		$stmt = DB_Query($sql1, $db);
		$obj = DB_fetch_object($stmt);
		$total = $obj->c;
		DB_free_result($result);
		 
		$sql2 .= "SELECT * FROM(".$sql.") AS A ";
		if(isset($start) && isset($limit)){
			$sql2 .= "WHERE (A.rownum BETWEEN ($start) AND ($start + $limit))";
		}
		$sql2 .= " ORDER BY A.exchange_imported_date, A.exchange_price, A.song_number";

		$result = DB_Query($sql2, $db);
		if (DB_has_rows($result)==0) {
            return null;
        } else {
            return $result;
        }
	}
	
	/* Get Sum Yield Revenue */
	function GetSumYieldRevenue($PartnerId = 2, $PartnerPrice = '', $ObjectId, $Type = 1, $FromDate, $ToDate, 
	$SingerAuthority = null, $ComposerAuthority = null, $ReleaseAuthority = null, $user, $password) {
		$Errors = array();
        $db = db($user, $password);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        }
		
		$sql = "SELECT SUM(exchange_quantity) AS sum_exchange_quantity, 
					SUM(exchange_price*exchange_quantity) as sum_money_total,
					SUM((exchange_price*exchange_quantity)*0.4) as sum_money_composer,
					SUM((exchange_price*exchange_quantity)*0.3) as sum_money_singer,
					SUM((exchange_price*exchange_quantity)*0.3) as sum_money_release
				FROM tblvgt_exchange
				WHERE locked = 0 AND exchange_imported_date >= '$FromDate'
					AND exchange_imported_date <= '$ToDate'";
		// neu tim ca sy
		if(isset($ObjectId) && ($ObjectId > 0)){
			if($Type == 1){
				$sql .= " AND singer_id = $ObjectId";
			}elseif($Type == 2){
				$sql .= " AND composer_id = $ObjectId";
			}
		}
		if(isset($PartnerId) && ($PartnerId > 0)){			
			$sql .= " AND partner_id = $PartnerId";			
		}
		if(isset($SingerAuthority)){			
			$sql .= " AND song_singer_authority = $SingerAuthority";			
		}
		if(isset($ComposerAuthority)){			
			$sql .= " AND song_composer_authority = $ComposerAuthority";			
		}
		if(isset($ReleaseAuthority)){			
			$sql .= " AND song_release_authority = $ReleaseAuthority";			
		}
		if(!empty($PartnerPrice)) {
			$sql .= " AND exchange_price = $PartnerPrice";
		}
		$result = DB_Query($sql, $db);
		if (DB_has_rows($result)==0) {
            return null;
        } else {
            return $result;
        }
	}
	
	/* Get Not Yield Revenue */
	function GetNotYieldRevenue($PartnerId = 2, $PartnerPrice, $ObjectId, $Type = 1, $FromDate, $ToDate, 
	$SingerAuthority = null, $ComposerAuthority = null, $ReleaseAuthority = null, $limit = null, $start = null, &$total, 
	$FromCreatedDate = null, $ToCreatedDate = null, $SongNumber = '', $SongName = '', $user, $password) {
		$Errors = array();
        $db = db($user, $password);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        }
		
		$sqlExchange = "SELECT DISTINCT song_id	
						FROM tblvgt_exchange 
						WHERE locked = 0 AND exchange_imported_date >= '$FromDate'
							AND exchange_imported_date <= '$ToDate'";
		if(isset($PartnerPrice) && !empty($PartnerPrice)){
			$sqlExchange .= "AND exchange_price = $PartnerPrice";
		}
		$whereExchange = array();		
		if(isset($PartnerId) && ($PartnerId > 0)){			
			$where[] = "s.partner_id = $PartnerId";						
		}
		if(isset($ObjectId) && ($ObjectId > 0)){
			if($Type == 1){
				$where[] = "singer_id = $ObjectId";
			}elseif($Type == 2){
				$where[] = "composer_id = $ObjectId";
			}
		}
		if(isset($SingerAuthority)){			
			$where[] = "song_singer_authority = $SingerAuthority";			
		}
		if(isset($ComposerAuthority)){			
			$where[] = "song_composer_authority = $ComposerAuthority";			
		}
		if(isset($ReleaseAuthority)){			
			$where[] = "song_release_authority = $ReleaseAuthority";			
		}
		$sqlExchange .= " AND ".implode(" AND ", $where);
		//////////////////////
		$sqlNotYield = "SELECT row_number() OVER (ORDER BY s.song_id) AS rownum,
							s.song_id, 
							s.type_id ,
							s.partner_id,
							s.singer_id,
							oSinger.object_number as singer_number,
							oComposer.object_number as composer_number,				
							s.composer_id,
							s.song_number,
							s.song_name,
							s.song_unsigned_name,
							s.song_price,
							s.song_singer_sing,
							s.song_singer_authority,
							s.song_composer_authority,
							s.song_release_authority,
							oSinger.object_name AS singer_name,
							oComposer.object_name As composer_name,
							oPartner.partner_name,
							convert(varchar, s.ended_date, 101) as ended_date,
							convert(varchar, s.created_date, 101) as created_date,
							convert(varchar, s.updated_date, 101) as updated_date
						FROM tblvgt_song as s
						LEFT JOIN tblvgt_object AS oSinger ON s.singer_id = oSinger.object_id
						LEFT JOIN tblvgt_object AS oComposer ON s.composer_id = oComposer.object_id
						LEFT JOIN tblvgt_partner AS oPartner ON s.partner_id = oPartner.partner_id						
						WHERE s.locked = 0 AND s.song_id NOT IN ($sqlExchange)".
						" AND ".implode(" AND ", $where);
		if (isset($FromCreatedDate) && ! empty($FromCreatedDate)) {
            $sqlNotYield .= " AND s.created_date >= '$FromCreatedDate'";
        }
        if (isset($ToCreatedDate) && ! empty($ToCreatedDate)) {
            $sqlNotYield .= " AND s.created_date <= '$ToCreatedDate'";
        }
		if (isset($SongNumber) && ! empty($SongNumber)) {
            $sqlNotYield .= " AND s.song_number LIKE '%$SongNumber%'";
        }
		if (isset($SongName) && ! empty($SongName)) {
            $sqlNotYield .= " AND s.song_name LIKE '%$SongName%'";
        }
		
		$sql1 .= "SELECT count(*) as c FROM(".$sqlNotYield.") AS A";
		$stmt = DB_Query($sql1, $db);
        $obj = DB_fetch_object($stmt);
        $total = $obj->c;        
		DB_free_result($result);
		$sql2 .= "SELECT * FROM(".$sqlNotYield.") AS A ";
		if(isset($start) && isset($limit)){
			$sql2 .= "WHERE (A.rownum BETWEEN ($start) AND ($start + $limit))";
		}
		$sql2 .= " ORDER BY created_date ASC";
		$result = DB_Query($sql2, $db);
		if (DB_has_rows($result)==0) {		
			return null;
        } else {
            return $result;
        }		
	}
	
	/* 
	 * get row.	 
     */
    function getRow($id) {
    	//Check user permission
		$db = db($_SESSION['UserID'],  $_SESSION['Password']);        
        $sql = "SELECT * FROM ".self::table_name." WHERE exchange_id = $id";
        $row = DB_query($sql, $db);
        return DB_fetch_array($row);        
    }
    
	/* 
	 * Verify field existed.	 
     */
    function verifyFieldExist($field, $value) {
    	//Check user permission
		$db = db($_SESSION['UserID'],  $_SESSION['Password']);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        };
        $Searchsql = "SELECT count(*) as c FROM ".self::table_name." WHERE $field = N'$value'";
        $SearchResult = DB_query($Searchsql, $db);
        $answer = DB_fetch_array($SearchResult);
        if ($answer['c'] != 0) {
            return 1;
        };
        return 0;
    }
    
	/* 
	* update 
	* huudatxm
	*/
    function update($data) {
        $Errors = array();
        $db = db($_SESSION['UserID'],  $_SESSION['Password']);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        }
        $exchange_id = $data['exchange_id'];
        $checkId = $this->verifyFieldExist('exchange_id',$exchange_id);
		if($checkId == 0) return 0;

		$val_str = $data['field']. ' = N\''.$data['value'].'\',';        		
		$val_str .= ' updated_date = N\''.date('Y-m-d').'\' ';        
		$val_str .= ', updated_by = N\''.$_SESSION['UserID'].'\' ';

		$sql = "UPDATE ".self::table_name." SET $val_str WHERE exchange_id = $exchange_id";
		
		if (sizeof($Errors) == 0) {			
            $result = DB_Query($sql, $db);
            if($result) return true;   
        }
        return 0;
    }
    
	/* 
	* delete 
	* huudatxm
	*/
    function delete($exchange_id, $user, $password) {        
        $Errors = array();
        $db = db($user, $password);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        }
        $checkId = $this->verifyFieldExist('exchange_id',$exchange_id);
		if($checkId == 1) {
			$val_str = 'locked = 1';
			$val_str .= ', updated_date = N\''.date('Y-m-d').'\' ';        
			$val_str .= ', updated_by = N\''.$_SESSION['UserID'].'\' ';	        		
	        $sql = "UPDATE ".self::table_name." SET $val_str WHERE exchange_id = $exchange_id";
			
	        if (sizeof($Errors) == 0) {			
	            return DB_Query($sql, $db);
	        }
		}
        return null;
    }
    
/* Get Summary Yield Revenue */
	function GetSummaryYieldRevenue($PartnerId = 2, $FromDate, $ToDate, &$list_detail, &$list_song, $user, $password) {
		$Errors = array();
        $db = db($user, $password);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        }
		
        // GET LIST DETAIL
		$sql = "SELECT 
					exchange_id, dbo.fnFormatDate (exchange_imported_date,'MM/YYYY') as exchange_imported_date, 
					song_number, song_name, exchange_quantity 				
				FROM tblvgt_exchange
				WHERE locked = 0 AND exchange_imported_date >= '$FromDate'
					AND exchange_imported_date <= '$ToDate'" . " 
					AND partner_id = $PartnerId
				ORDER BY exchange_imported_date";
		$list_detail = DB_Query($sql, $db);
		
		// GET 
		$sql = "SELECT 
					tblvgt_exchange.song_number, MAX(tblvgt_exchange.song_name) as song_name, 
				 	MAX (tblvgt_exchange.singer_name) as singer_name,  
					MAX (tblvgt_exchange.composer_name) as composer_name, 
				 	SUM(tblvgt_exchange.exchange_quantity) as exchange_quantity
				FROM tblvgt_exchange				
				WHERE tblvgt_exchange.locked = 0 
				AND tblvgt_exchange.exchange_imported_date >= '$FromDate' 
				AND tblvgt_exchange.exchange_imported_date <= '$ToDate' 
				AND tblvgt_exchange.partner_id = " . $PartnerId . "				
				group by tblvgt_exchange.song_number, tblvgt_exchange.song_name
				ORDER BY tblvgt_exchange.song_number";
		$list_song = DB_Query($sql, $db);
		
		if (DB_has_rows($list_detail)==0 && DB_has_rows($list_song)==0) {
            return false;
        } else {
            return true;
        }
	}
	
	
	
	 
    /* 
     * author: trungpm
     */
    function GetSongsNerverExchanged($partnerId, $user, $password) {
        $db = db($user, $password);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        }		
        $sql = 'SELECT song.song_id,
			song.song_number,
			song.song_name,
			song.song_unsigned_name
		FROM tblvgt_song as song				
		WHERE song.partner_id = '.$partnerId.' AND song.song_number NOT IN (
			SELECT DISTINCT song_number FROM tblvgt_exchange WHERE partner_id = ' . $partnerId . ')
		ORDER BY song.song_number';
        return DB_Query($sql, $db);
    }
}